from mysql_conn.mysql_conn import MysqlConn
import pandas as pd

clock_daily_mysql = MysqlConn('10.3.1.154', 'root', 'S0beydbaAt2o2o', 'clock_daily', port=3307)
smart_card_mysql = MysqlConn('10.3.1.154', 'root', 'S0beydbaAt2o2o', 'smart_card', port=3307)

sql_clock = f"SELECT uid, address, province, area, city, created, date, geo_api_info, fxyy FROM ncov_report_daily where date=20201208"
sql_user = f"SELECT uid, user_number from user_role"
clock_daily_data = clock_daily_mysql.read_mysql('ncov_report_daily', sql_clock)
role_data = clock_daily_mysql.read_mysql('user_role', sql_user)
print(clock_daily_data)
print(role_data)
clock_daily_data1 = pd.merge(clock_daily_data, role_data, on='uid', how='left')

print(clock_daily_data1)

sql_smart_deal = f"SELECT PERSONNO, MACHINEID, DEALDATETIME, CARDBALANCE, TRANSMONEY, DEALNAME from mid_deal_202012_g where DEALDATETIME > '2020-12-08' and DEALDATETIME < '2020-12-09'"
sql_smart_account = f"SELECT PERSONNO, PERSONNAME, SEX, STATUSNAME, DEPTNAME, IDNO, ENDCODE from mid_cardaccount1209 where ENDCODE=0"
sql_smart_machine = f"SELECT MACHINEID, MACHINENAME, ORGNAME, MACHINESTATUS from mid_machine1209"
smart_data = smart_card_mysql.read_mysql('mid_deal_202012_g', sql_smart_deal)
smart_account = smart_card_mysql.read_mysql('mid_cardaccount1209', sql_smart_account)
smart_machine = smart_card_mysql.read_mysql('mid_machine1209', sql_smart_machine)

print(smart_data)
print(smart_account)
print(smart_machine)
